<?php
/**
 * Created by PhpStorm.
 * User: wangkxin@foxmail.com
 * Date: 2022/7/13
 * Time: 14:02
 */

namespace app\index\controller;


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Office
{
    public function index()
    {

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('丹阳市宏观经济企业信息填报系统上报数据导出表');

        //表头
        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, ' 丹阳市宏观经济企业信息填报系统上报数据导出表');
        $worksheet->setCellValueByColumnAndRow(1, 2, '企业名称');
        $worksheet->setCellValueByColumnAndRow(2, 2, '法定代表人');
        $worksheet->setCellValueByColumnAndRow(3, 2, '上报日期');
        $worksheet->setCellValueByColumnAndRow(4, 2, '填报开始日期');
        $worksheet->setCellValueByColumnAndRow(5, 2, '填报结束日期');
        $worksheet->setCellValueByColumnAndRow(6, 2, '完成占比');
        $worksheet->setCellValueByColumnAndRow(7, 2, '是否完成');
        $worksheet->setCellValueByColumnAndRow(8, 2, '统一社会代码');
        //设置列宽
        $worksheet->getColumnDimension('A')->setWidth(20);
        $worksheet->getColumnDimension('B')->setWidth(20);
        $worksheet->getColumnDimension('C')->setWidth(20);
        $worksheet->getColumnDimension('D')->setWidth(20);
        $worksheet->getColumnDimension('E')->setWidth(20);
        $worksheet->getColumnDimension('F')->setWidth(20);
        $worksheet->getColumnDimension('G')->setWidth(20);
        $worksheet->getColumnDimension('H')->setWidth(20);

        //合并单元格
        $worksheet->mergeCells('A1:H1');
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单元格样式
        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

        $worksheet->getStyle('A2:H2')->applyFromArray($styleArray)->getFont()->setSize(14);

        //获取全部企业上报的数据 进行excel导出

        $jzInfo = [
           [
                'en_name'=>'gggg',
                'fictitious'=>'55',
                'report_month'=>'55',
                'start_time'=>'66jjh',
                'end_time'=>'jhjjhjh',
                'uniform_code'=>'80898767',
                'complete_acc'=>'fff',
                'is_complete'=>0
            ],
            [
                'en_name'=>'gggg',
                'fictitious'=>'55',
                'report_month'=>'55',
                'start_time'=>'66jjh',
                'end_time'=>'jhjjhjh',
                'uniform_code'=>'80898767',
                'complete_acc'=>'fff',
                'is_complete'=>0
            ],
            [
                'en_name'=>'gggg',
                'fictitious'=>'55',
                'report_month'=>'55',
                'start_time'=>'66jjh',
                'end_time'=>'jhjjhjh',
                'uniform_code'=>'80898767',
                'complete_acc'=>'fff',
                'is_complete'=>0
            ]
        ];
        $len = count($jzInfo);
        $j = 0;
        for ($i=0; $i < $len; $i++) {
            $j = $i + 3; //从表格第3行开始

            $worksheet->setCellValueByColumnAndRow(1, $j, $jzInfo[$i]['en_name']);
            $worksheet->setCellValueByColumnAndRow(2, $j, $jzInfo[$i]['fictitious']);
            $worksheet->setCellValueByColumnAndRow(3, $j, $jzInfo[$i]['report_month']);
            $worksheet->setCellValueByColumnAndRow(4, $j, $jzInfo[$i]['start_time']);
            $worksheet->setCellValueByColumnAndRow(5, $j, $jzInfo[$i]['end_time']);
            $worksheet->setCellValueByColumnAndRow(6, $j, $jzInfo[$i]['complete_acc']);
            if($jzInfo[$i]['is_complete'] == 0){
                $worksheet->setCellValueByColumnAndRow(7, $j, '否');
            }else{
                $worksheet->setCellValueByColumnAndRow(7, $j, '是');
            }
            $worksheet->setCellValueByColumnAndRow(8, $j, $jzInfo[$i]['uniform_code']);
        }

        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $total_jzInfo = $len + 2;
        //添加所有边框/居中
        $worksheet->getStyle('A1:H'.$total_jzInfo)->applyFromArray($styleArrayBody);

        $filename = '丹阳市宏观经济企业信息填报系统上报数据导出表.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }



    public function export2()
    {

        $name = date('Y-m-d') . '人员列表信息';
        $data = [
            [
                'id'=>'1',
                'name'=>'hu',
                'create_at'=>'2021-12-04'
            ],
            [
                'id'=>'2',
                'name'=>'bu',
                'create_at'=>'2020-12-04'
            ],
            [
                'id'=>'3',
                'name'=>'wei',
                'create_at'=>'2017-07-04'
            ],
        ];
        $head = ['ID', '姓名', '添加时间'];
        //数据中对应的字段，用于读取相应数据：
        $keys = ['id', 'name', 'create_at'];
        $this->MyExcelExpotrs($name,$data,$head,$keys);
    }







    /**
     * 封装成方法,
     * 任意数据导出使用
     */
    private function MyExcelExpotrs($name='表名', $data=[], $head=[], $keys=[])
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setTitle($name);//设置当前工作表标题

        //设置表头
        foreach ($head as $key => $value) {
            $sheet->setCellValueByColumnAndRow($key+1, 1, $value);
        }

        //设置内容
        foreach ($data as $row=>$item) {
            foreach ($keys as $key=>$value) {
                $sheet->setCellValueByColumnAndRow($key+1, $row+2, $item[$value]);
            }
        }
        $writer = new Xlsx($spreadsheet);
        //合并单元格 合并后，赋值只能给A1，开始的坐标。
        $sheet->mergeCells('A1:C1');//合并单元格
        //$sheet->mergeCellsByColumnAndRow(1,2,1,3);//使用数字单元格坐标合并单元格
        // $sheet->unmergeCellsByColumnAndRow(1,2,1,3);//使用数字单元格坐标拆分单元格

        //1.下载到服务器
        //$writer->save($name.'.xlsx');

        //2.输出到浏览器
        //header('Content-Type: application/vnd.ms-excel');//xls
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//xlsx
        header('Content-Disposition: attachment;filename="'.$name.'.xlsx"');
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }


















}